package edu.xalead;

import org.junit.Test;

import java.sql.*;
import java.util.Scanner;


public class Test1 {
    /**
     * 测试查询
     */
    @Test
    public void testQuery(){
        //mysql驱动类
        String driverClass = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://192.168.93.88:3306/mydb";
        String username = "root";
        String password = "";
        //数据库连接接口
        Connection conn = null;
        //执行sql语句的接口
        Statement st = null;
        //操作数据库返回的查询结果的接口
        ResultSet rs = null;
        try {
            //加载驱动类到虚拟机
            Class.forName(driverClass);

            //连接数据库
            conn = DriverManager.getConnection(url,username,password);

            //创建语句对象
            st = conn.createStatement();

            String job = "CLERK";//接收职务
            //执行查询SQL语句
            String sql = "select e.empno,e.ename ename,e.job,d.dname dname " +
                    "from emp e join dept d where e.deptno = d.deptno " +
                    "and job = '" + job + "'";
            rs = st.executeQuery(sql); //拿到查询的结果集了

            //操作
            while(rs.next()){
                //取列的值
                System.out.println(rs.getInt(1));
                System.out.println(rs.getString("ename"));
                System.out.println(rs.getString(3));
                System.out.println(rs.getString("dname"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }  finally {
            try {
                if(st != null)
                    st.close();
                if(conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 测试PrepareStatement对象
     */
    @Test
    public void testPrepare(){
        //mysql驱动类
        String driverClass = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://192.168.93.88:3306/mydb";
        String username = "root";
        String password = "";
        //数据库连接接口
        Connection conn = null;
        //执行sql语句的接口
        PreparedStatement prst = null;
        //操作数据库返回的查询结果的接口
        ResultSet rs = null;
        try {

            //加载驱动类到虚拟机
            Class.forName(driverClass);
            //连接数据库
            conn = DriverManager.getConnection(url,username,password);
            String sql = "insert into student(code,name,sex,phone,age,enter_time) values(?,?,?,?,?,?)";
            //创建语句对象
            prst = conn.prepareStatement(sql);

            prst.setInt(1,1243);
            prst.setString(2,"lisi");
            prst.setString(3,"M");
            prst.setString(4,"127346754");
            prst.setInt(5,23);
            prst.setDate(6,new Date(new java.util.Date().getTime()));

            prst.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        }  finally {
            try {
                if(prst != null)
                    prst.close();
                if(conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 测试PrepareStatement对象
     */
    @Test
    public void testPrepare0(){
        //mysql驱动类
        String driverClass = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://192.168.93.88:3306/mydb";
        String username = "root";
        String password = "";
        //数据库连接接口
        Connection conn = null;
        //执行sql语句的接口
        PreparedStatement prst = null;
        //操作数据库返回的查询结果的接口
        ResultSet rs = null;
        try {

            //加载驱动类到虚拟机
            Class.forName(driverClass);
            //连接数据库
            conn = DriverManager.getConnection(url,username,password);
            String sql = "update student set name = ? where code = ?";
            //创建语句对象
            prst = conn.prepareStatement(sql);

            prst.setString(1,"杨明亮");
            prst.setInt(2,1113);

            prst.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        }  finally {
            try {
                if(prst != null)
                    prst.close();
                if(conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 测试PrepareStatement对象
     */
    @Test
    public void testPrepare00(){
        //mysql驱动类
        String driverClass = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://192.168.93.88:3306/mydb";
        String username = "root";
        String password = "";
        //数据库连接接口
        Connection conn = null;
        //执行sql语句的接口
        PreparedStatement prst = null;
        //操作数据库返回的查询结果的接口
        ResultSet rs = null;
        try {

            //加载驱动类到虚拟机
            Class.forName(driverClass);
            //连接数据库
            conn = DriverManager.getConnection(url,username,password);
            String sql = "delete from student where name = ? and sex = ?";
            //创建语句对象
            prst = conn.prepareStatement(sql);

            prst.setString(1,"杨明亮");
            prst.setString(2,"男");

            prst.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        }  finally {
            try {
                if(prst != null)
                    prst.close();
                if(conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 测试PrepareStatement对象
     */
    @Test
    public void testPrepare1(){
        //mysql驱动类
        String driverClass = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://192.168.93.88:3306/mydb";
        String username = "root";
        String password = "";
        //数据库连接接口
        Connection conn = null;
        //执行sql语句的接口
        PreparedStatement prst = null;
        //操作数据库返回的查询结果的接口
        ResultSet rs = null;
        try {

            //加载驱动类到虚拟机
            Class.forName(driverClass);
            //连接数据库
            conn = DriverManager.getConnection(url,username,password);
            String sql = "select e.ename,e.job,d.dname " +
                        "from emp e join dept d on e.deptno = d.deptno " +
                        "where e.job = ?";
            //创建语句对象
            prst = conn.prepareStatement(sql);

            prst.setString(1,"CLERK");

            rs = prst.executeQuery(); //拿到查询的结果集了

            //操作
            while(rs.next()){
                //取列的值
                System.out.println(rs.getString(1));
                System.out.println(rs.getString("ename"));
                System.out.println(rs.getString(3));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }  finally {
            try {
                if(prst != null)
                    prst.close();
                if(conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
